package com.kexun.dao.impl;

import com.kexun.dao.StuDao;
import com.kexun.pojo.ScoreCou;
import com.kexun.pojo.Student;
import com.kexun.util.DruidUtil;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StuDaoImpl implements StuDao {
    private JdbcTemplate t = new JdbcTemplate(DruidUtil.getDataSource());

    @Override
    public Student findStuInfo(String stuNum) {
      String sql ="select * from student where stu_num=?";
      Student student = null;
      try{
           student = t.queryForObject(sql, new BeanPropertyRowMapper<>(Student.class), stuNum);
      }catch (DataAccessException e){
          return  null;
      }
      return student;
    }

    /**
     * 成绩信息查看
     *
     * @param stuNum
     * @return
     */
    @Override
    public Map<String, Object> findScore(String stuNum,int page,int limit) {

        //查询当前用户各的成绩
        String sql ="select s.score_grade,s.major,c.course_name,c.course_hours,c.course_credit,c.course_teacher,c.coursedate from score s ,course c where s.stu_num =? and s.course_name = c.course_name limit ?,? ";

        String sqlCount ="select count(*) from score s ,course c where s.stu_num =? and s.course_name = c.course_name";
        List<ScoreCou> list = null;
        int i = 0 ;

        Map<String,Object> map =new HashMap<>();

        try {
           list = t.query(sql, new BeanPropertyRowMapper<>(ScoreCou.class), stuNum, (page-1)*limit, limit);
           i = t.queryForObject(sqlCount, Integer.class, stuNum);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);
        return map;
    }

    /**
     * 精确查找课程
     *
     * @param stuNum
     * @param page
     * @param limit
     * @param coursename
     * @return
     */
    @Override
    public Map<String, Object> findCourse(String stuNum, int page, int limit, String coursename) {


        String sql ="select s.score_grade,s.major,c.course_name,c.course_hours,c.course_credit,c.course_teacher,c.coursedate from score s ,course c where s.stu_num =? and s.course_name = c.course_name and c.course_name = ? limit ? ,?";

        String sqlCount ="select count(*) from score s ,course c where s.stu_num =? and c.course_name =? and s.course_name = c.course_name;";

        List<ScoreCou> list = null;
        int i = 0;
        try {
            list = t.query(sql, new BeanPropertyRowMapper<>(ScoreCou.class), stuNum,coursename, (page-1)*limit, limit);
            i = t.queryForObject(sqlCount, Integer.class, stuNum,coursename);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        System.out.println(list);

        Map<String,Object> map = new HashMap<>();
        map.put("code",0);
        map.put("msg","");
        map.put("count",i);
        map.put("data",list);
        System.out.println(map);
        return map;

    }

    /**
     * 修改学生信息
     *
     * @param student
     * @return
     */
    @Override
    public int update(Student student) {

        //        String sql1 = "update student set stu_name='" + student.getStuName() + "',stu_age="+student.getStuAge()+",stu_sex='"+student.getStuSex()+"',stu_class='"+student.getStuClass()+"',major='"+student.getMajor()+"',department='"+student.getDepartment()+"' where stu_num="+student.getStuNum();

//        int stuId = student.getStuId();
        int stuAge = student.getStuAge();   //当强制转换成int类型以便和数据库的age类型一致方便查时，浏览器会报500错误，什么都不做最好
        String stuNum = student.getStuNum();
        String stuName = student.getStuName();
        String stuSex = student.getStuSex();
        String stuClass = student.getStuClass();
        String major = student.getMajor();
        String department = student.getDepartment();

        //不可以用id作为where后查询的条件，
        String sql = "update student set stu_name ='" + stuName + "',stu_sex='" + stuSex + "',stu_age="+stuAge +"," + "stu_class='" + stuClass + "',major='" + major + "',department='" + department + "'where stu_num ="+student.getStuNum();

        int i = t.update(sql);
        return i;



    }
}
